package com.chenenjun.dao;

import java.io.StringReader;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.chenenjun.db.C3P0DB;
import com.chenenjun.vo.Article;
import com.chenenjun.vo.BBSUser;
import com.chenenjun.vo.PageBean;
import com.chenenjun.vo.ReArticle;

public class ArticleDAOImpl implements IArticleDAO {
	private Connection conn = C3P0DB.getConnection();
	@Override
	public List<Article> queryArticles() {
		// TODO Auto-generated method stub
		String sql = "select * from article a, bbsuser b where a.userid = b.id and a.rootid = 0 order by a.id desc";
		List<Article> list = new ArrayList<Article>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Article article = new Article();
				article.setTitle(rs.getString("a.title"));
				article.setContent(rs.getString("a.content"));
				article.setDatetime(rs.getString("a.datetime"));
				article.setId(rs.getInt("a.id"));
				article.setRootid(rs.getInt("a.rootid"));
				
				BBSUser user = new BBSUser();
				user.setId(rs.getInt("b.id"));
				user.setPAGENUM(rs.getInt("b.PAGENUM"));
				article.setUser(user);
				
				list.add(article);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(pstmt != null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return list;
	}
	@Override
	public PageBean queryAByP(int curPage, int userid) {
		// TODO Auto-generated method stub
		String sql = "{call p_1(?, ?, ?, ?, ?)}";
		CallableStatement cs = null;
		ArrayList<Article> list = new ArrayList<Article>();
		PageBean pageBean = null;
		ResultSet rs = null;
		try {
			cs = conn.prepareCall(sql);
			cs.setString(1, userid+"");
			cs.setInt(2, curPage);
			pageBean = new PageBean();
			cs.registerOutParameter(3, java.sql.Types.INTEGER);
			cs.registerOutParameter(4, java.sql.Types.INTEGER);
			cs.registerOutParameter(5, java.sql.Types.INTEGER);
			boolean flag = cs.execute();
			while(flag){
				rs = cs.getResultSet();
				while(rs.next()){
					Article article = new Article();
					article.setTitle(rs.getString("a.title"));
					article.setContent(rs.getString("a.content"));
					article.setDatetime(rs.getString("a.datetime"));
					article.setId(rs.getInt("a.id"));
					article.setRootid(rs.getInt("a.rootid"));
					
					BBSUser user = new BBSUser();
					user.setId(rs.getInt("b.id"));
					user.setUsername(rs.getString("b.username"));
					user.setPAGENUM(rs.getInt("b.PAGENUM"));
					article.setUser(user);
					
					list.add(article);
				} 
				pageBean.setData(list);
				pageBean.setCurPage(curPage);
				pageBean.setMaxPage(cs.getInt(3));
				pageBean.setMaxRowCount(cs.getInt(4));
				pageBean.setRowsPerPage(cs.getInt(5));
				flag = cs.getMoreResults();
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(cs != null){
				try {
					cs.close();
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return pageBean;
	}
	@Override
	public boolean delArticle(int id, int rootid) {
		// TODO Auto-generated method stub
		String sql = "delete from article where id = ? "+(rootid == 0?" or rootid = ?":"");
		PreparedStatement pstmt = null;
		boolean flag = false;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			/*
			 *  当传进来rootid等于0的时候说明要删除的是主贴，所以从贴也要一起删掉，
			 * 否则就是删除从贴，这时只要根据传进来的帖子的主键id删除当前的帖子即可。
			*/
			if(rootid == 0){
				pstmt.setInt(2, id);
			}
			flag = pstmt.executeUpdate() > 0 ? true : false;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			if(pstmt != null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return flag;
	}
	@Override
	public boolean addArticle(Article a) {
		// TODO Auto-generated method stub
		String sql = "insert into article (rootid, title, content, userid, datetime) values(?, ?, ?, ?, now())";
		PreparedStatement pstmt = null;
		StringReader reader = null;
		boolean flag = false;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, a.getRootid());
			pstmt.setString(2, a.getTitle());
			//往数据库中存入大的文本数据使用clob
			reader = new StringReader(a.getContent());
			pstmt.setCharacterStream(3, reader, a.getContent().length());
			pstmt.setInt(4, a.getUser().getId());
			flag = pstmt.executeUpdate() > 0 ? true : false;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			if(reader != null){
				reader.close();
			}
			if(pstmt != null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return flag;
	}
	@Override
	public ReArticle queryArticlesById(int id) {
		// TODO Auto-generated method stub
		String sql = "select * from article a, bbsuser b where (a.rootid = ? or a.id = ?) and a.userid = b.id order by a.id";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Article> list = new ArrayList<Article>();
		ReArticle rearticle = new ReArticle();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.setInt(2, id);
			rs = pstmt.executeQuery();
			if(rs.next()){// 得到主贴的标题
				rearticle.setTitle(rs.getString("a.title"));
			}
			while(rs.next()){// 此时while循环里面全都是从贴
				Article article = new Article();
				article.setTitle(rs.getString("a.title"));
				article.setContent(rs.getString("a.content"));
				article.setDatetime(rs.getString("a.datetime"));
				article.setId(rs.getInt("a.id"));
				article.setRootid(rs.getInt("a.rootid"));
				
				BBSUser user = new BBSUser();
				user.setId(rs.getInt("b.id"));
				user.setPAGENUM(rs.getInt("b.PAGENUM"));
				article.setUser(user);
				
				list.add(article);
			}
			rearticle.setList(list);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(pstmt != null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return rearticle;
	}
}
